package net.vinote.smart.platform.module.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;

import net.vinote.smart.platform.base.Platform;
import net.vinote.smart.platform.comm.PlatformConst;
import net.vinote.smart.platform.exception.PlatformException;
import net.vinote.smart.platform.module.database.bo.BizObject;
import net.vinote.smart.platform.module.database.bo.DBQuery;
import net.vinote.smart.platform.module.log.RunLogger;


public class DBManager {
	private static final RunLogger logger = RunLogger.getLogger();

	private String table;

	private Connection conn;

	private DBTransaction innerTx;

	private boolean closed = false;

	public DBManager(String table) {
		this.table = table;
	}

	/**
	 * @param fields
	 *            查询结果返回字段
	 * @param whereSql
	 *            查询条件
	 * @param paramVal
	 *            预编译参数值
	 * @param unionTable
	 *            关联表
	 * @return
	 */
	public DBQuery createQuery(String[] fields, String whereSql,
			String[] paramVal, String unionTable) {
		checkState();
		StringBuffer sb = new StringBuffer("select ");
		for (int i = 0; i < fields.length; i++) {
			if (fields[i].startsWith("F.")) {// 过滤数据库函数
				sb.append(fields[i].substring(2));
			} else {
				sb.append(fields[i]);
			}
			if (i < fields.length - 1)
				sb.append(",");
		}
		sb.append(" from ").append(table).append(" O");
		if (unionTable != null && unionTable.trim().length() > 0) {
			sb.append(",").append(unionTable);
		}
		if (whereSql != null && whereSql.trim().length() > 0) {
			sb.append(" where ").append(whereSql);
		}
		DBQuery q = new DBQuery();
		q.setFiledsSeq(fields);
		q.setConnection(conn);
		q.setParamVal(paramVal);
		q.setQuerySql(sb.toString());
		return q;
	}

	/**
	 * @param fields
	 *            被查询的字段
	 * @param whereSql
	 *            where条件
	 * @return
	 * @throws SQLException
	 */
	public DBQuery createQuery(String[] fields, String whereSql)
			throws SQLException {
		return createQuery(fields, whereSql, "");
	}

	/**
	 * @param fields
	 *            被查询的字段
	 * @param whereSql
	 *            where条件
	 * @param unionTable
	 *            从表
	 * @return
	 * @throws SQLException
	 */
	public DBQuery createQuery(String[] fields, String whereSql,
			String unionTable) throws SQLException {
		return createQuery(fields, whereSql, null, unionTable);
	}

	/**
	 * 删除一条记录
	 * 
	 * @throws SQLException
	 **/
	public int delete(String[] key, String... val) throws SQLException {
		StringBuffer sb = new StringBuffer();
		if (key != null) {
			int keySize = key.length;
			for (int i = 0; i < keySize; i++) {
				sb.append(key[i]).append("=?");
				if (i < keySize - 1)
					sb.append(" and ");
			}
		}
		return deleteWhereSql(sb.toString(), val);
	}

	public int deleteWhereSql(String whereSql) throws SQLException {
		return deleteWhereSql(whereSql, new String[] {});
	}

	/**
	 * @param whereSql
	 * @param sqlValue
	 *            对应whereSql中的占位符
	 * @return
	 * @throws SQLException
	 */
	public int deleteWhereSql(String whereSql, String... sqlValue)
			throws SQLException {
		checkState();
		int sReturn = PlatformConst.SQL_UNDO;
		initInnerTranstraction();
		StringBuffer sb = new StringBuffer("delete from ");
		sb.append(table);
		if (whereSql != null && whereSql.length() > 0) {
			sb.append(" where ").append(whereSql);
		}
		StringBuffer valSB = new StringBuffer("[");
		PreparedStatement ps = conn.prepareStatement(sb.toString());
		if (sqlValue != null) {
			int length = sqlValue.length;
			for (int i = 0; i < length; i++) {
				valSB.append(sqlValue[i]).append(",");
				ps.setString(i + 1, sqlValue[i]);
			}
		}
		valSB.append("]");
		logger.log(Level.FINER,
				"SQL:" + sb.toString() + " param:" + valSB.toString());
		sReturn = ps.executeUpdate();
		destoryInnerTranstraction();
		ps.close();
		ps = null;
		return sReturn;
	}

	/**
	 * 更新字段,
	 * 
	 * @param fields
	 *            被更新的字段
	 * @param whereSql
	 *            where条件
	 * @param paramVal
	 *            预编译占位值
	 * @throws SQLException
	 */
	public int update(String[] fields, String whereSql, String... paramVal)
			throws SQLException {
		checkState();
		int effectRows = 0;
		initInnerTranstraction();
		StringBuffer sb = new StringBuffer("update ");
		sb.append(table).append(" set ");
		int i = 0;
		int length = fields.length;
		for (String f : fields) {
			sb.append(f).append("=? ");
			if (++i < length) {
				sb.append(",");
			}
		}
		sb.append(" where ").append(whereSql);
		logger.log(Level.FINER, sb.toString());
		PreparedStatement ps = conn.prepareStatement(sb.toString());
		i = 0;
		for (String v : paramVal) {
			ps.setObject(++i, v);
		}
		effectRows = ps.executeUpdate();
		destoryInnerTranstraction();
		ps.close();
		ps = null;
		return effectRows;
	}

	/**
	 * 回收内部事务资源
	 * 
	 * @throws SQLException
	 */
	private void destoryInnerTranstraction() throws SQLException {
		if (innerTx != null) {
			innerTx.commit();
			innerTx.close();
			conn = null;
		}
	}

	/***
	 * 在没有外部事务传入的情况下启动内部事务
	 */
	private void initInnerTranstraction() {
		if (conn == null) {
			innerTx = DBFactory.getFactory().getTransaction();
			conn = innerTx.getConn();
		}
	}

	public void saveBizObject(BizObject bo) throws SQLException {
		checkState();
		initInnerTranstraction();
		StringBuffer sb = new StringBuffer("insert into ");
		sb.append(table).append("(");
		Set<String> attrs = bo.getAttributeNames();
		StringBuffer valSb = new StringBuffer();
		int i = 0;
		int setSize = attrs.size();
		for (String attr : attrs) {
			sb.append(attr);
			valSb.append("?");
			if (++i < setSize) {
				sb.append(",");
				valSb.append(",");
			}
		}
		sb.append(") values(").append(valSb.toString()).append(")");
		logger.log(Level.FINER, sb.toString());
		PreparedStatement ps = conn.prepareStatement(sb.toString());
		i = 0;
		StringBuffer paramValStr = new StringBuffer("param[");
		for (String attr : attrs) {
			ps.setObject(++i, bo.getAttribute(attr));
			paramValStr.append(i).append(":").append(bo.getAttribute(attr))
					.append(" ,");
		}
		paramValStr.append("]");
		logger.log(Level.FINER, paramValStr.toString());
		ps.executeUpdate();
		ps.close();
		destoryInnerTranstraction();
		ps = null;
	}

	public void saveBizObjectBatch(List<BizObject> list) throws SQLException {
		checkState();
		initInnerTranstraction();
		PreparedStatement ps = null;
		for (BizObject bo : list) {
			StringBuffer sb = new StringBuffer("insert into ");
			sb.append(table).append("(");
			Set<String> attrs = bo.getAttributeNames();
			StringBuffer valSb = new StringBuffer();
			int i = 0;
			int setSize = attrs.size();
			for (String attr : attrs) {
				sb.append(attr);
				valSb.append("?");
				if (++i < setSize) {
					sb.append(",");
					valSb.append(",");
				}
			}
			sb.append(") values(").append(valSb.toString()).append(")");
			ps = conn.prepareStatement(sb.toString());
			i = 0;
			StringBuffer paramValStr = new StringBuffer("param[");
			for (String attr : attrs) {
				ps.setObject(++i, bo.getAttribute(attr));
				paramValStr.append(i).append(":").append(bo.getAttribute(attr))
						.append(" ,");
			}
			ps.executeUpdate();
			ps.close();
			ps = null;
			paramValStr.append("]");
		}
		destoryInnerTranstraction();
	}

	/**
	 * 更新指定对象的数据至数据库,若该对象的主键不完整,则抛异常 进行更新操作时,传入的bo对象中必须包含了对应表的所有主键并主键值不位空,
	 * 若该表不存在主键,则无法进行更新操作,并抛异常.
	 * 
	 * @param bo
	 * @throws SQLException
	 */
	public void updateBizObject(BizObject bo) throws SQLException {
		checkState();
		initInnerTranstraction();
		validateKey(bo);
		List<String> fieldVal = new ArrayList<String>();
		List<String> keyVal = new ArrayList<String>();
		Table tableBo = Platform.getAttribute(PlatformConst.SRE_PREFIX_TABLE
				+ table, Table.class);
		StringBuffer sb = new StringBuffer("update ");
		sb.append(table).append(" set ");
		Set<String> attrs = bo.getAttributeNames();
		StringBuffer whereSql = new StringBuffer();
		for (String attr : attrs) {
			if (tableBo.isKey(attr)) {
				whereSql.append(attr).append("=? and ");
				keyVal.add(bo.getAttribute(attr));
			} else {
				sb.append(attr).append("=? ,");
				fieldVal.add(bo.getAttribute(attr));
			}
		}
		sb.deleteCharAt(sb.length() - 1).append(" where ").append(whereSql)
				.append(" 1=1");
		PreparedStatement ps = conn.prepareStatement(sb.toString());
		int i = 0;
		StringBuffer paramValStr = new StringBuffer("param[");
		for (String f : fieldVal) {
			ps.setObject(++i, f);
			paramValStr.append(i).append(":").append(f).append(" ,");
		}
		for (String f : keyVal) {
			ps.setObject(++i, f);
			paramValStr.append(i).append(":").append(f).append(" ,");
		}
		paramValStr.append("]");
		logger.log(Level.FINEST, sb.toString() + "" + paramValStr);
		ps.executeUpdate();
		ps.close();
		destoryInnerTranstraction();
		ps = null;
	}

	/**
	 * 校验主键的合法性
	 * 
	 * @param bo
	 */
	private void validateKey(BizObject bo) {
		List<Field> keyList = Platform.getAttribute(
				PlatformConst.SRE_PREFIX_TABLE + table, Table.class).getKey();
		if (keyList == null || keyList.size() == 0) {
			throw new PlatformException("该表不存在主键!");
		}
		for (Field f : keyList) {
			String keyVal = bo.getAttribute(f.getFieldName());
			if (keyVal == null || "".equals(keyVal.trim())) {
				throw new PlatformException("缺失主键值!");
			}
		}
	}

	private void checkState() {
		if (closed) {
			throw new PlatformException("DBManager is closed");
		}
	}

	/**
	 * 仅同包下的类可设置conection
	 * 
	 * @param conn
	 */
	void setConn(Connection conn) {
		this.conn = conn;
	}

	public boolean isClosed() {
		return closed;
	}

	public void close() {
		this.closed = false;
	}

}
